﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DVSProject.Models;
using System.Data;

namespace DVSProject.Lib
{
    public class LocationMachineSetRepo
    {
        public const string m_msg_head = "LocationMachineSet";

        public IEnumerable<LocationMachineSetModels> GetLocationMachineSet()
        {
            List<LocationMachineSetModels> LocationMachineSets = new List<LocationMachineSetModels>();
            string SQLCommand = @"select TBLocationMachineSet.*,isnull(TBLocationSet.LocationSetCode,'') +'-'+isnull(TBLocationSet.LocSetNameTh,'') AS LocSet_Name,
                                         isnull(TBLocationMachineSet.SerialNo,'')+'<'+isnull(TBModel.NameTh,'') +'>'+ isnull(TBMachine.NameTh,'') AS Machine_Name
                                  from TBLocationMachineSet LEFT JOIN TBLocationSet
                                          ON TBLocationMachineSet.LocationSetCode = TBLocationSet.LocationSetCode LEFT JOIN TBMachine
                                          ON TBLocationMachineSet.SerialNo = TBMachine.SerialNo LEFT JOIN TBModel 
                                          ON TBMachine.ModelId = TBModel.ModelId
                                 WHERE TBLocationMachineSet.Status <> 'Delete'";
            using (var db = new DBHelper())
            {
                try
                {
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        LocationMachineSetModels LocationMachineSet = new LocationMachineSetModels();
                        LocationMachineSet.code_no = reader["code_no"].ToString().Trim();
                        LocationMachineSet.LocationSetCode = (string)reader["LocationSetCode"];
                        LocationMachineSet.LocSet_Name = (string)reader["LocSet_Name"];
                        LocationMachineSet.SerialNo = (string)reader["SerialNo"];
                        LocationMachineSet.Machine_Name = (string)reader["Machine_Name"];
                        LocationMachineSet.start_meter_no = (reader["start_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["start_meter_no"]) : 0;
                        LocationMachineSet.last_meter_no = (reader["last_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["last_meter_no"]) : 0;
                        LocationMachineSet.total_meter_value = (reader["total_meter_value"] != DBNull.Value) ? SF.GetDouble(reader["total_meter_value"]) : 0;
                        LocationMachineSet.contract_start_date = (reader["contract_start_date"] != DBNull.Value) ? SF.GetDateDDMMYYYY((DateTime)reader["contract_start_date"]) : "";//, new System.Globalization.CultureInfo("en-US"));
                        LocationMachineSet.remark = (!string.IsNullOrEmpty(reader["remark"].ToString())) ? (string)reader["remark"] : "";
                        LocationMachineSet.Status = (string)reader["Status"];
                        LocationMachineSet.CreateBy = (string)reader["CreateBy"];
                        LocationMachineSet.CreateOn = (DateTime)reader["CreateOn"];
                        LocationMachineSet.UpdateBy = (string)reader["UpdateBy"];
                        LocationMachineSet.UpdateOn = (DateTime)reader["UpdateOn"];

                        LocationMachineSets.Add(LocationMachineSet);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return LocationMachineSets.ToList();
        }

        public TransLocationMachineSetModels GetLocationMachineSetById(string id)
        {
            TransLocationMachineSetModels TransLocationMachineSet = new TransLocationMachineSetModels();
            string SQLCommand = @"select * from TBLocationMachineSet where code_no=@code_no";

            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@code_no", id.Trim());
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                        TransLocationMachineSet.tmp_code_no = SF.GetString(reader["code_no"].ToString().Trim());
                        TransLocationMachineSet.tmp_LocationSetCode = SF.GetString(reader["LocationSetCode"].ToString().Trim());
                        TransLocationMachineSet.tmp_SerialNo = "1";// (string)reader["SerialNo"];
                        TransLocationMachineSet.tmp_start_meter_no = 0; //(reader["start_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["start_meter_no"]) : 0;
                        TransLocationMachineSet.tmp_last_meter_no = 0; //(reader["last_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["last_meter_no"]) : 0;
                        TransLocationMachineSet.tmp_total_meter_value = 0;// (reader["total_meter_value"] != DBNull.Value) ? SF.GetDouble(reader["total_meter_value"]) : 0;
                        //TransLocationMachineSet.tmp_CreateBy = (string)reader["CreateBy"];
                        //TransLocationMachineSet.tmp_CreateOn = (DateTime)reader["CreateOn"];
                        //TransLocationMachineSet.tmp_RentBasic = (reader["RentBasic"] != DBNull.Value) ? SF.GetDouble(reader["RentBasic"]) : 0;
                        //TransLocationMachineSet.tmp_RentForUse = (reader["RentForUse"] != DBNull.Value) ? SF.GetDouble(reader["RentForUse"]) : 0;
                        //TransLocationMachineSet.tmp_ConversionRate = (reader["ConversionRate"] != DBNull.Value) ? (int)reader["ConversionRate"] : 0;
                        TransLocationMachineSet.ref_FSR_NO = "";
                        TransLocationMachineSet.ref_SerialNo =(string)reader["SerialNo"];
                        TransLocationMachineSet.ref_start_meter_no = (reader["start_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["start_meter_no"]) : 0;
                        TransLocationMachineSet.ref_last_meter_no = (reader["last_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["last_meter_no"]) : 0;
                        TransLocationMachineSet.ref_total_meter_value = (reader["total_meter_value"] != DBNull.Value) ? SF.GetDouble(reader["total_meter_value"]) : 0;
                        TransLocationMachineSet.ref_CreateBy = (string)reader["CreateBy"];
                        TransLocationMachineSet.ref_CreateOn = (DateTime)reader["CreateOn"];

                        //TransLocationMachineSet.UpdateBy = (string)reader["UpdateBy"];
                        //TransLocationMachineSet.UpdateOn = (DateTime)reader["UpdateOn"];
                        TransLocationMachineSet.remarks = (!string.IsNullOrEmpty(reader["remark"].ToString())) ? (string)reader["remark"] : "";
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
                }
            }
            return TransLocationMachineSet;
        }
        public LocationMachineSetModels GetLocationMachineSetForEditById(string id)
        {
            LocationMachineSetModels LocationMachineSet = new LocationMachineSetModels();
            string SQLCommand = @"select * from TBLocationMachineSet where code_no=@code_no";

            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@code_no", id.Trim());
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                        LocationMachineSet.code_no = SF.GetString(reader["code_no"].ToString().Trim());
                        LocationMachineSet.LocationSetCode = SF.GetString(reader["LocationSetCode"].ToString().Trim());
                        LocationMachineSet.SerialNo = (string)reader["SerialNo"];
                        LocationMachineSet.start_meter_no = (reader["start_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["start_meter_no"]) : 0;
                        LocationMachineSet.last_meter_no = (reader["last_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["last_meter_no"]) : 0;
                        LocationMachineSet.total_meter_value = (reader["total_meter_value"] != DBNull.Value) ? SF.GetDouble(reader["total_meter_value"]) : 0;
                        LocationMachineSet.contract_start_date = (reader["contract_start_date"] != DBNull.Value) ? SF.GetDateDD_MM_YYYY((DateTime)reader["contract_start_date"]) : "";// ConvertMMddYYYY(LocationMachineSet.contract_start_date.ToString()));

                        LocationMachineSet.remark = (!string.IsNullOrEmpty(reader["remark"].ToString())) ? (string)reader["remark"] : "";
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
                }
            }
            return LocationMachineSet;
        }
        public IEnumerable<LocationMachineSetModels> GetLocationMachineSetByLocationSetCode(string locationSetCode)
        {
            List<LocationMachineSetModels> LocationMachineSets = new List<LocationMachineSetModels>();
            string SQLCommand = @"select TBLocationMachineSet.*,TBLocationSet.LocationSetCode +'-'+TBLocationSet.LocSetNameTh AS LocSet_Name,
                                        isnull(TBLocationMachineSet.SerialNo,'')+'<'+isnull(TBModel.NameTh,'') +'>'+ isnull(TBMachine.NameTh,'') AS Machine_Name
                                        --  TBLocationMachineSet.SerialNo AS Machine_Name
                                  from TBLocationMachineSet LEFT JOIN TBLocationSet
                                          ON TBLocationMachineSet.LocationSetCode = TBLocationSet.LocationSetCode LEFT JOIN TBMachine
                                          ON TBLocationMachineSet.SerialNo = TBMachine.SerialNo LEFT JOIN TBModel 
                                          ON TBMachine.ModelId = TBModel.ModelId
                                 WHERE TBLocationMachineSet.LocationSetCode =@locationSetCode and TBLocationMachineSet.Status <> 'Delete'";
            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@locationSetCode", locationSetCode.Trim());
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        LocationMachineSetModels LocationMachineSet = new LocationMachineSetModels();
                        LocationMachineSet.code_no = reader["code_no"].ToString().Trim();
                        LocationMachineSet.LocationSetCode = (string)reader["LocationSetCode"];
                        LocationMachineSet.LocSet_Name = (string)reader["LocSet_Name"];
                        LocationMachineSet.SerialNo = (string)reader["SerialNo"];
                        LocationMachineSet.Machine_Name = (string)reader["Machine_Name"];
                        LocationMachineSet.start_meter_no = (reader["start_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["start_meter_no"]) : 0;
                        LocationMachineSet.last_meter_no = (reader["start_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["start_meter_no"]) : 0;
                        LocationMachineSet.total_meter_value = (reader["total_meter_value"] != DBNull.Value) ? SF.GetDouble(reader["total_meter_value"]) : 0;
                        LocationMachineSet.contract_start_date = (reader["contract_start_date"] != DBNull.Value) ? SF.GetDateDDMMYYYY((DateTime)reader["contract_start_date"]) : "";
                        LocationMachineSet.remark = (!string.IsNullOrEmpty(reader["remark"].ToString())) ? (string)reader["remark"] : "";
                        LocationMachineSet.Status = (string)reader["Status"];
                        LocationMachineSet.CreateBy = (string)reader["CreateBy"];
                        LocationMachineSet.CreateOn = (DateTime)reader["CreateOn"];
                        LocationMachineSet.UpdateBy = (string)reader["UpdateBy"];
                        LocationMachineSet.UpdateOn = (DateTime)reader["UpdateOn"];

                        LocationMachineSets.Add(LocationMachineSet);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return LocationMachineSets.ToList();
        }

        public bool Create(LocationMachineSetModels LocationMachineSet)
        {
            bool result = true;
            string SQLCommand = @"DECLARE @CODE_NO1	varchar(7)
                                DECLARE @LASTNO		int
                                DECLARE @RentBasic decimal(9,2)
                                DECLARE @RentForUse numeric(9,2)
                                DECLARE @ConversionRate int
                                DECLARE @refFSR_NO char(10)
                                DECLARE @last_meter_no numeric(18,0)
                                DECLARE @start_meter_no numeric(18,0)
                                
                                SET @RentBasic = (select RentBasic   from VModelMachine WHERE  Status = 'Active' AND  SerialNo = @SerialNo)
                                SET @RentForUse = (select RentForUse   from VModelMachine WHERE  Status = 'Active' AND  SerialNo = @SerialNo)
                                SET @ConversionRate = (select ConversionRate   from VModelMachine WHERE  Status = 'Active' AND  SerialNo = @SerialNo)
                                SET @refFSR_NO = (SELECT Top 1 FSR_NO FROM TBVisitHD WHERE Status <> 'Active' AND SerialNo = @SerialNo ORDER BY  CreateOn DESC) 
                                SET @last_meter_no = (select last_meter_no   from VModelMachine WHERE  Status = 'Active' AND  SerialNo = @SerialNo)
                                SET @start_meter_no = (select start_meter_no   from VModelMachine WHERE  Status = 'Active' AND  SerialNo = @SerialNo)    
                                
                                SET @LASTNO =(select isnull(max(right(isnull(code_no ,0),5)),0)+1   from TBLocationMachineSet)
                                SET @CODE_NO1='LM' + STUFF('00000', len('00000')-(len(@LASTNO)-1), len(@LASTNO),@LASTNO)

                                INSERT into TBLocationMachineSet 
                                    values (@CODE_NO1,@LocationSetCode,@SerialNo,@start_meter_no,@last_meter_no,@total_meter_value,@contract_start_date,@remark,@Status,@UserId,
                                    GETDATE(),@UserId,GETDATE())
                                    
                                INSERT into TBTransLocationMachineSet 
                                   values ('NEW',@CODE_NO1,@LocationSetCode,@SerialNo,@start_meter_no,@last_meter_no,@total_meter_value,@UserId,
                                    GETDATE(),@RentBasic,@RentForUse,@ConversionRate,@refFSR_NO,null,null,null,null,null,null,null)";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    //db.AddParameter("@code_no", LocationMachineSet.code_no);
                    db.AddParameter("@LocationSetCode", LocationMachineSet.LocationSetCode);
                    db.AddParameter("@SerialNo", LocationMachineSet.SerialNo);
                    //db.AddParameter("@start_meter_no", LocationMachineSet.start_meter_no);
                    //db.AddParameter("@last_meter_no", LocationMachineSet.last_meter_no);
                    db.AddParameter("@total_meter_value", LocationMachineSet.total_meter_value);
                    db.AddParameter("@contract_start_date", ConvertMMddYYYY(LocationMachineSet.contract_start_date));//.ToString("MM/dd/yyyy", new System.Globalization.CultureInfo("th-TH")));//ConvertMMddYYYY(LocationMachineSet.contract_start_date.ToString()));
                    db.AddParameter("@remark", LocationMachineSet.remark);
                    db.AddParameter("@Status", "Active");
                    db.AddParameter("@UserID", LocationMachineSet.CreateBy );// Session["Employee"]

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);


                    // var return_num =    db.ExecuteScalar(SQLCommand, DbConnectionState.KeepOpen);

                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }
            return result;
        }
        private string ConvertMMddYYYY(string date)
        {
             date = date.Substring(0, 10);
             string[] splitdate = date.Split('-');
             //Int32 y = Convert.ToInt32(splitdate[2]);
             date = splitdate[1] + "/" + splitdate[0] + "/" + splitdate[2];// Convert.ToString(2);
            return date;
        }
        private string ConvertYYYYMMdd(string date)
        {
            date = date.Substring(0, 10);
            string[] splitdate = date.Split('/');
            //Int32 y = Convert.ToInt32(splitdate[2]);
            date = splitdate[2] + "/" + splitdate[1] + "/" + splitdate[0];// Convert.ToString(2);
            return date;
        }
        public bool ExistsDupSerialNo(string SerialNo)
        {
            bool result = false;
            string SQLCommand = @"Select  SerialNo From TBLocationMachineSet   Where Status='Active' and SerialNo=@SerialNo";

            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@SerialNo", SerialNo);
                    DataTable Dt = db.ExecuteDataSet(SQLCommand).Tables[0];
                    if (Dt.Rows.Count > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }
            return result;
        }
        public bool ExistsDupLocSetId(string LocationSetCode)
        {
            bool result = false;
            string SQLCommand = @"Select  LocationSetCode From TBLocationMachineSet   Where Status='Active' and LocSetId=@LocSetId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@LocationSetCode", LocationSetCode);
                    DataTable Dt = db.ExecuteDataSet(SQLCommand).Tables[0];
                    if (Dt.Rows.Count > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }
            return result;
        }
        public bool Edit(TransLocationMachineSetModels TransLocationMachineSet)
        {
            bool result = true;
            string SQLCommand = @"DECLARE @RentBasic decimal(9,2)
                                DECLARE @RentForUse numeric(9,2)
                                DECLARE @ConversionRate int
                                DECLARE @refFSR_NO char(10)
                                DECLARE @last_meter_no numeric(18,0)
                                DECLARE @start_meter_no numeric(18,0)

                                SET @RentBasic = (select RentBasic   from VModelMachine WHERE  Status = 'Active' AND  SerialNo = @tmp_SerialNo)
                                SET @RentForUse = (select RentForUse   from VModelMachine WHERE  Status = 'Active' AND  SerialNo = @tmp_SerialNo)
                                SET @ConversionRate = (select ConversionRate   from VModelMachine WHERE  Status = 'Active' AND  SerialNo = @tmp_SerialNo)
                                SET @refFSR_NO = (SELECT Top 1 FSR_NO FROM TBVisitHD WHERE Status <> 'Active' AND SerialNo = @ref_SerialNo ORDER BY  CreateOn DESC)
                                SET @last_meter_no = (select last_meter_no   from VModelMachine WHERE  Status = 'Active' AND  SerialNo = @tmp_SerialNo)
                                SET @start_meter_no = (select start_meter_no   from VModelMachine WHERE  Status = 'Active' AND  SerialNo = @tmp_SerialNo)    

                                INSERT into TBTransLocationMachineSet 
                                values (@Event,@tmp_code_no,@tmp_LocationSetCode,@tmp_SerialNo,@last_meter_no,@last_meter_no,@ref_total_meter_value,@tmp_CreateBy,
                                GETDATE(),@RentBasic,@RentForUse,@ConversionRate,@refFSR_NO,@ref_SerialNo,@ref_start_meter_no,@ref_last_meter_no,@ref_total_meter_value,@ref_CreateBy,GETDATE(),@remarks)
 
                                UPDATE TBLocationMachineSet 
                                    Set SerialNo = @tmp_SerialNo,
                                        start_meter_no = @start_meter_no,
                                        last_meter_no = @last_meter_no,
                                        total_meter_value=@ref_total_meter_value,
                                        remark=@remarks,
                                        UpdateBy=@tmp_CreateBy,UpdateOn=getdate()
                                    where code_no=@tmp_code_no ";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@Event", "CHANGE");
                    db.AddParameter("@tmp_code_no", TransLocationMachineSet.tmp_code_no.Trim());
                    db.AddParameter("@tmp_LocationSetCode", TransLocationMachineSet.tmp_LocationSetCode.Trim());
                    db.AddParameter("@tmp_SerialNo", TransLocationMachineSet.tmp_SerialNo.Trim());
                    //db.AddParameter("@tmp_start_meter_no", TransLocationMachineSet.tmp_start_meter_no);
                    //db.AddParameter("@tmp_last_meter_no", TransLocationMachineSet.tmp_last_meter_no);
                    db.AddParameter("@tmp_total_meter_value", TransLocationMachineSet.tmp_total_meter_value);
                    db.AddParameter("@tmp_CreateBy", TransLocationMachineSet.tmp_CreateBy);

                    //db.AddParameter("@tmp_CreateOn", TransLocationMachineSet.tmp_CreateOn);
                    //db.AddParameter("@tmp_RentBasic", TransLocationMachineSet.tmp_RentBasic);
                    //db.AddParameter("@tmp_RentForUse", TransLocationMachineSet.tmp_RentForUse);
                    //db.AddParameter("@tmp_ConversionRate", TransLocationMachineSet.tmp_ConversionRate);
                    //db.AddParameter("@ref_FSR_NO", TransLocationMachineSet.ref_FSR_NO.Trim());
                    db.AddParameter("@ref_SerialNo", TransLocationMachineSet.ref_SerialNo.Trim());

                    db.AddParameter("@ref_start_meter_no", TransLocationMachineSet.ref_start_meter_no);
                    db.AddParameter("@ref_last_meter_no", TransLocationMachineSet.ref_last_meter_no);
                    db.AddParameter("@ref_total_meter_value", TransLocationMachineSet.ref_total_meter_value);

                    db.AddParameter("@ref_CreateBy", TransLocationMachineSet.ref_CreateBy);
                   // db.AddParameter("@ref_CreateOn", TransLocationMachineSet.ref_CreateOn);
                    db.AddParameter("@remarks", TransLocationMachineSet.remarks);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }
            return result;
        }

        public bool EditOld(LocationMachineSetModels LocationMachineSet)
        {
            bool result = true;
            string SQLCommand = @"UPDATE TBLocationMachineSet 
                                    Set  start_meter_no = @last_meter_no,
                                        last_meter_no = @last_meter_no,
                                        total_meter_value=@total_meter_value,
                                        contract_start_date=@contract_start_date,
                                        remark=@remark,
                                        UpdateBy=@UpdateBy,UpdateOn=getdate()
                                    where code_no=@code_no ";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@code_no", LocationMachineSet.code_no.Trim());
                    db.AddParameter("@start_meter_no", LocationMachineSet.start_meter_no);
                    db.AddParameter("@last_meter_no", LocationMachineSet.last_meter_no);
                    db.AddParameter("@total_meter_value", LocationMachineSet.total_meter_value);
                    db.AddParameter("@contract_start_date", ConvertMMddYYYY(LocationMachineSet.contract_start_date));//.ToString("MM/dd/yyyy", new System.Globalization.CultureInfo("th-TH")));
                    // ConvertMMddYYYY(LocationMachineSet.contract_start_date.ToString("dd/MM/yyyy", new System.Globalization.CultureInfo("en-US"))));
                    db.AddParameter("@UpdateBy", LocationMachineSet.UpdateBy);

                    db.AddParameter("@remark", LocationMachineSet.remark);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }
            return result;
        }
        public bool Delete(LocationMachineSetModels LocationMachineSet)
        {
            bool result = true;
            string SQLCommand = @"Update TBLocationMachineSet 
                                    Set Status=@Status,UpdateBy=@UserId,UpdateOn=getdate()
                                    where code_no=@code_no";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@code_no", LocationMachineSet.code_no.Trim());
                    db.AddParameter("@Status", "Delete");
                    db.AddParameter("@UserID", LocationMachineSet.UpdateBy );

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0005", ex));
                    return false;
                }
            }
            return result;
        }
    }
}